SQL 2 Variable
==============

This little utility is used to format a long SQL statement
so that it can be assigned to an Access Basic or Visual
Basic variable in small chunks. Rather than assigning one 
long string to the variable you can break it up into pieces.
This makes the code much easier to read. 

For example, this program will reformat this:

TRANSFORM
    Sum([Current Month Transactions].POUNDS) AS SumOfPOUNDS
SELECT
  PRODUCTS.COPRODUCT, 
  [Current Month Transactions].LOCATION_CODE,
  LOCATION_CODES.LOCATION_DESC,
  [Current Month Transactions].PRODUCT_CODE,
  [Current Month Transactions].PACKAGE_TYPE,
  PRODUCTS.PRODUCT_NAME, 
  PACKAGE_TYPE_CODES.PACKAGE_DESC
FROM [Current Month Transactions], 
     PRODUCTS,
     PACKAGE_TYPE_CODES,
     LOCATION_CODES,
     [Current Month Transactions] 
INNER JOIN PRODUCTS ON
    [Current Month Transactions].PRODUCT_CODE = 
    PRODUCTS.PRODUCT_CODE,
    [Current Month Transactions]
INNER JOIN PACKAGE_TYPE_CODES ON
    [Current Month Transactions].PACKAGE_TYPE = 
    PACKAGE_TYPE_CODES.PACKAGE_TYPE,
    [Current Month Transactions] 
INNER JOIN LOCATION_CODES ON
    [Current Month Transactions].LOCATION_CODE = 
    LOCATION_CODES.LOCATION_CODE
GROUP BY PRODUCTS.COPRODUCT, 
    [Current Month Transactions].LOCATION_CODE,
    LOCATION_CODES.LOCATION_DESC, 
    [Current Month Transactions].PRODUCT_CODE,
    [Current Month Transactions].PACKAGE_TYPE,
    PRODUCTS.PRODUCT_NAME, 
    PACKAGE_TYPE_CODES.PACKAGE_DESC
ORDER BY PRODUCTS.COPRODUCT, 
    [Current Month Transactions].LOCATION_CODE,
    LOCATION_CODES.LOCATION_DESC, 
    [Current Month Transactions].PRODUCT_CODE, 
    [Current Month Transactions].PACKAGE_TYPE
PIVOT [Current Month Transactions].TRANSACTION_CODE 
  In ("EI","S","TR","REC","BI","SL","SS","OG")
WITH OWNERACCESS OPTION;

to:

sql = sql & "TRANSFORM "
sql = sql & "Sum([Current Month Transactions].POUNDS) AS SumOfPOUNDS "
sql = sql & "SELECT "
sql = sql & "PRODUCTS.COPRODUCT, "
sql = sql & "[Current Month Transactions].LOCATION_CODE, "
sql = sql & "LOCATION_CODES.LOCATION_DESC, "
sql = sql & "[Current Month Transactions].PRODUCT_CODE, "
sql = sql & "[Current Month Transactions].PACKAGE_TYPE, "
sql = sql & "PRODUCTS.PRODUCT_NAME, "
sql = sql & "PACKAGE_TYPE_CODES.PACKAGE_DESC "
sql = sql & "FROM [Current Month Transactions], "
sql = sql & "PRODUCTS, "
sql = sql & "PACKAGE_TYPE_CODES, "
sql = sql & "LOCATION_CODES, "
sql = sql & "[Current Month Transactions] "
sql = sql & "INNER JOIN PRODUCTS ON "
sql = sql & "[Current Month Transactions].PRODUCT_CODE = "
sql = sql & "PRODUCTS.PRODUCT_CODE, "
sql = sql & "[Current Month Transactions] "
sql = sql & "INNER JOIN PACKAGE_TYPE_CODES ON "
sql = sql & "[Current Month Transactions].PACKAGE_TYPE = "
sql = sql & "PACKAGE_TYPE_CODES.PACKAGE_TYPE, "
sql = sql & "[Current Month Transactions] "
sql = sql & "INNER JOIN LOCATION_CODES ON "
sql = sql & "[Current Month Transactions].LOCATION_CODE = "
sql = sql & "LOCATION_CODES.LOCATION_CODE "
sql = sql & "GROUP BY PRODUCTS.COPRODUCT, "
sql = sql & "[Current Month Transactions].LOCATION_CODE, "
sql = sql & "LOCATION_CODES.LOCATION_DESC, "
sql = sql & "[Current Month Transactions].PRODUCT_CODE, "
sql = sql & "[Current Month Transactions].PACKAGE_TYPE, "
sql = sql & "PRODUCTS.PRODUCT_NAME, "
sql = sql & "PACKAGE_TYPE_CODES.PACKAGE_DESC "
sql = sql & "ORDER BY PRODUCTS.COPRODUCT, "
sql = sql & "[Current Month Transactions].LOCATION_CODE, "
sql = sql & "LOCATION_CODES.LOCATION_DESC, "
sql = sql & "[Current Month Transactions].PRODUCT_CODE, "
sql = sql & "[Current Month Transactions].PACKAGE_TYPE "
sql = sql & "PIVOT [Current Month Transactions].TRANSACTION_CODE "
sql = sql & "In ("
sql = sql & chr$(34) & "EI"
sql = sql & chr$(34) & ","
sql = sql & chr$(34) & "S"
sql = sql & chr$(34) & ","
sql = sql & chr$(34) & "TR"
sql = sql & chr$(34) & ","
sql = sql & chr$(34) & "REC"
sql = sql & chr$(34) & ","
sql = sql & chr$(34) & "BI"
sql = sql & chr$(34) & ","
sql = sql & chr$(34) & "SL"
sql = sql & chr$(34) & ","
sql = sql & chr$(34) & "SS"
sql = sql & chr$(34) & ","
sql = sql & chr$(34) & "OG"
sql = sql & chr$(34) & ") "
sql = sql & "WITH OWNERACCESS OPTION; "

In some cases you might want to do a little cleanup of the
generated code, but this utility should save you a little bit
of typing if you often copy Access SQL statements into either
Access Basic or Visual Basic code.

This utility is free. It requires the run-time file VBRUN300.DLL
which is not included. You can find this file on the MSBASIC,
WINFUN, or WINSHARE forums on Compuserve.

Jim Ferguson
CIS: 71477,2345